#!/usr/bin/python 2.5

import psycopg2
import threading

from common.singleton import Singleton
from common import config

class DatabaseControllor:
    __metaclass__ = Singleton
    
    def __init__(self):
        self.db_dbname = getattr(config, 'DB_DBNAME')
        self.db_user = getattr(config, 'DB_USER')
        self.db_password = getattr(config, 'DB_PASSWORD')
        self.db_host = getattr(config, 'DB_HOST')
        self.db_port = getattr(config, 'DB_PORT')

    def connect(self):
        try:
            self.conn = psycopg2.connect("dbname=" + self.db_dbname + \
                                        " user=" + self.db_user + " password=" + self.db_password + \
                                        " host=" + self.db_host + " port=" + self.db_port)
            self.cur = self.conn.cursor()
            #print "Connect to DataBase Success"
            return True
        except StandardError, err:
            print "Connection Error:", err

    def disconnect(self):
        self.conn.close()
        #print "Disconnet to DataBase"
        return True

    def createTable(self, table, attributes):
        try:
            str = "("
            for name, type in attributes.items():
                str = str + name + " " + type + ", "
            table_attri = str[0:-2] + ")"
            self.cur.execute('CREATE TABLE ' + table + table_attri)
            self.conn.commit()
            print "Create Table", table, "Success"
        except StandardError, err:
            print "Create Table", table, "Error:", err

    def dropTable(self, table):
        try:
            self.cur.execute("drop table " + table + ';')
            self.conn.commit()
            #print "Drop Table", table, "Success"
            return True
        except StandardError, err:
            print "Insert into", table, "Error:", err

    def add(self, table, values):
        try:
            self.cur.execute("insert into " + table + " values(" + values + ");")
            self.conn.commit()
            #print "Insert into", table, "Success"
            return True
        except StandardError, err:
            print "Inert into", table, "Error:", err

    def delete(self, table, conditions = ""):
        try:
            if conditions == "":
                self.cur.execute("delete from " + table)
            else:
                self.cur.execute("delete from " + table + " where " + conditions)
            self.conn.commit()
            #print "Delete from", table, "Success"
            return True
        except StandardError, err:
            print "Delete from", table, "Error:", err

    def update(self, table, newvalues, conditions, locations = ""):
        try:
            if locations == "":
                self.cur.execute("update " + table + \
                                " set " + newvalues + \
                                " where " + conditions)
            else:
                self.cur.execute("update " + table + \
                                " set " + newvalues + \
                                " from " + locations + \
                                " where " + conditions)
            self.conn.commit()
            #print "Update", table, "Success"
            return True
        except StandardError, err:
            print "Update", table, "Error:", err

    def select(self, attributes, tables, conditions = ""):
        try:
            if conditions == "":
                self.cur.execute("select " + attributes + \
                                " from " + tables)
            else:
                self.cur.execute("select " + attributes + \
                                " from " + tables + 
                                " where " + conditions)
            result = self.cur.fetchall()
            return result
            #print "Query Success"
        except StandardError, err:
            print "Query Error:", err
    
    #display certain data from a table
    #in fact, we give out the result of some "select" query
    def display(self, tables, attributes, conditions = ""):
        rows = self.select(attributes, tables, conditions)
        for row in rows:
            print row
        return True

    #this operation is used to execute other operation that we do not provide here
    #be sure that you have given the correct SQL
    def executeSQL(self, sql):
        try:
            result = self.cur.execute(sql)
            self.conn.commit()
            #print "Execute SQL Success"
            return result
        except StandardError, err:
            print "Execute SQL Erro:", err

    def executeFile(self, sql_file):
        sqlf = file(sql_file, 'r')
        dbname = sqlf.readline().split(":")[1]
        user = sqlf.readline().split(":")[1]
        password = sqlf.readline().split(":")[1]
        host = sqlf.readline().split(":")[1]
        port = sqlf.readline().split(":")[1]
        self.connect(dbname, user, password, host, port)
        line = sqlf.readline()
        while(line):
            self.cur.execute(line)
            line = sqlf.readline()
        self.cur.execute("commit")
        sqlf.close()

    def importDataset(self, dataset, table, attributes):
        #Please refer to dataset.data for example
        #Because we cannot add comment on data file, so we put the requirement of dataset file here
        #All data including attribute name and attribute type is splited by ','
        #No more line is added after the end of all data

        dataf = file(dataset, 'r')  #open the dataset file
        line = dataf.readline()
        while(line):
            str = ""
            for value in line.split(","):
                str = str + "'" + value + "'" + ","
            values = str[0:-3] + "'"    #prepare data form for insert into table values
            try:
                self.cur.execute("insert into " + table + \
                                "(" + attributes + ")" +\
                                " values(" + values + ");")
            except StandardError, err:
                print "Inert into", table, "Error:", err
            line = dataf.readline()

        self.conn.commit()
        return True
